package com.bootdo.system.controller;

import com.bootdo.common.annotation.Log;
import com.bootdo.common.config.BootdoConfig;
import com.bootdo.common.config.Constant;
import com.bootdo.common.controller.BaseController;
import com.bootdo.common.domain.Tree;
import com.bootdo.common.service.DictService;
import com.bootdo.common.utils.MD5Utils;
import com.bootdo.common.utils.PageUtils;
import com.bootdo.common.utils.Query;
import com.bootdo.common.utils.R;
import com.bootdo.system.domain.*;
import com.bootdo.system.service.DeptService;
import com.bootdo.system.service.RoleService;
import com.bootdo.system.service.UserService;
import com.bootdo.system.vo.UserVO;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RequestMapping("/sys/user")
@Controller
public class UserController extends BaseController {
	private String prefix="system/user";
	@Autowired
	UserService userService;
	@Autowired
	RoleService roleService;
	@Autowired
	DictService dictService;

	@Autowired
	private DeptService deptService;

	@Autowired
	private BootdoConfig bootdoConfig;

	@RequiresPermissions("sys:user:user")
	@GetMapping("")
	String user(Model model) {
		return prefix + "/user";
	}

	@GetMapping("/list")
	@ResponseBody
	PageUtils list(@RequestParam Map<String, Object> params) {
		// 查询列表数据
		if(null != params.get("flag") && !"".equals(params.get("flag")) && "1".equals(params.get("flag"))) {
			params.put("userId",getUserId());
		}
		Query query = new Query(params);
		List<UserDO> sysUserList = userService.list(query);
		if(CollectionUtils.isNotEmpty(sysUserList)) {
			DriverManagerDataSource dataSource=new DriverManagerDataSource();
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl("jdbc:mysql://" + bootdoConfig.getLocalDataBaseIp() + ":" + bootdoConfig.getRemoteDataBasePort() + "/" + bootdoConfig.getRemoteDataBaseName());
			dataSource.setUsername(bootdoConfig.getRemoteDataBaseUser());
			dataSource.setPassword(bootdoConfig.getRemoteDataBasePassword());
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			String sql = "select upload_authority,download_authority from baidujiex.upload_download_authority where user_id =";
			for(UserDO data : sysUserList) {
				List<UserExDO> list = jdbcTemplate.query(sql + data.getUserId(), new RowMapper() {
					@Override
					public Object mapRow(ResultSet resultSet, int i) throws SQLException {
						UserExDO userExDO = new UserExDO();
						userExDO.setDownloadAuthority(resultSet.getString("download_authority"));
						userExDO.setUploadAuthority(resultSet.getString("upload_authority"));
						return userExDO;
					}
				});
				if(CollectionUtils.isNotEmpty(list)) {
					data.setUploadAuthority(list.get(0).getUploadAuthority());
					data.setDownloadAuthority(list.get(0).getDownloadAuthority());
				}
			}
		}
		int total = userService.count(query);
		PageUtils pageUtil = new PageUtils(sysUserList, total);
		return pageUtil;
	}

	@RequiresPermissions("sys:user:add")
	@Log("添加用户")
	@GetMapping("/add")
	String add(Model model) {
		List<RoleDO> roles = roleService.list();
		model.addAttribute("roles", roles);
		return prefix + "/add";
	}

	@RequiresPermissions("sys:user:edit")
	@Log("编辑用户")
	@GetMapping("/edit/{id}")
	String edit(Model model, @PathVariable("id") Long id) {
		UserDO userDO = userService.get(id);
		if(CollectionUtils.isNotEmpty(userDO.getRoleIds())) {
			userDO.setRoleId(userDO.getRoleIds().get(0));
		}
		model.addAttribute("user", userDO);
		List<RoleDO> roles = roleService.list(id);
		model.addAttribute("roles", roles);
		return prefix+"/edit";
	}

	@RequiresPermissions("sys:user:add")
	@Log("保存用户")
	@PostMapping("/save")
	@ResponseBody
	R save(UserDO user) {
		user.setPassword(MD5Utils.encrypt(user.getUsername(), user.getPassword()));
		if (userService.save(user) > 0) {
			DriverManagerDataSource dataSource=new DriverManagerDataSource();
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl("jdbc:mysql://" + bootdoConfig.getLocalDataBaseIp() + ":" + bootdoConfig.getRemoteDataBasePort() + "/" + bootdoConfig.getRemoteDataBaseName());
			dataSource.setUsername(bootdoConfig.getRemoteDataBaseUser());
			dataSource.setPassword(bootdoConfig.getRemoteDataBasePassword());
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			String sql = "insert into baidujiex.upload_download_authority(user_id,upload_authority,download_authority,dept_id) values (" + user.getUserId() +",0,0,"+user.getDeptId()+")";
			jdbcTemplate.execute(sql);
			return R.ok();
		}
		return R.error();
	}

	@PostMapping("/saveFront")
	@ResponseBody
	R saveFront(UserDO user) {
		user.setPassword(MD5Utils.encrypt(user.getUsername(), user.getPassword()));
		if (userService.save(user) > 0) {
			DriverManagerDataSource dataSource=new DriverManagerDataSource();
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl("jdbc:mysql://" + bootdoConfig.getLocalDataBaseIp() + ":" + bootdoConfig.getRemoteDataBasePort() + "/" + bootdoConfig.getRemoteDataBaseName());
			dataSource.setUsername(bootdoConfig.getRemoteDataBaseUser());
			dataSource.setPassword(bootdoConfig.getRemoteDataBasePassword());
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			String sql = "insert into baidujiex.upload_download_authority(user_id,upload_authority,download_authority,dept_id) values (" + user.getUserId() +",0,0,"+user.getDeptId()+")";
			jdbcTemplate.execute(sql);
			return R.ok();
		}
		return R.error();
	}

	@RequiresPermissions("sys:user:edit")
	@Log("更新用户")
	@PostMapping("/update")
	@ResponseBody
	R update(UserDO user) {
		if (userService.update(user) > 0) {
			return R.ok();
		}
		return R.error();
	}


	@RequiresPermissions("sys:user:edit")
	@Log("更新用户")
	@PostMapping("/updatePeronal")
	@ResponseBody
	R updatePeronal(UserDO user) {
		if (userService.updatePersonal(user) > 0) {
			return R.ok();
		}
		return R.error();
	}


	@RequiresPermissions("sys:user:remove")
	@Log("删除用户")
	@PostMapping("/remove")
	@ResponseBody
	R remove(Long id) {
		if (userService.remove(id) > 0) {
			return R.ok();
		}
		return R.error();
	}

	@RequiresPermissions("sys:user:batchRemove")
	@Log("批量删除用户")
	@PostMapping("/batchRemove")
	@ResponseBody
	R batchRemove(@RequestParam("ids[]") Long[] userIds) {
		int r = userService.batchremove(userIds);
		if (r > 0) {
			return R.ok();
		}
		return R.error();
	}

	@PostMapping("/exit")
	@ResponseBody
	boolean exit(@RequestParam Map<String, Object> params) {
		// 存在，不通过，false
		return !userService.exit(params);
	}

	@RequiresPermissions("sys:user:resetPwd")
	@Log("请求更改用户密码")
	@GetMapping("/resetPwd/{id}")
	String resetPwd(@PathVariable("id") Long userId, Model model) {

		UserDO userDO = new UserDO();
		userDO.setUserId(userId);
		model.addAttribute("user", userDO);
		return prefix + "/reset_pwd";
	}

	@Log("提交更改用户密码")
	@PostMapping("/resetPwd")
	@ResponseBody
	R resetPwd(UserVO userVO) {
		if (Constant.DEMO_ACCOUNT.equals(getUsername())) {
			return R.error(1, "演示系统不允许修改,完整体验请部署程序");
		}
		try{
			userService.resetPwd(userVO,getUser());
			return R.ok();
		}catch (Exception e){
			return R.error(1,e.getMessage());
		}

	}
	@RequiresPermissions("sys:user:resetPwd")
	@Log("admin提交更改用户密码")
	@PostMapping("/adminResetPwd")
	@ResponseBody
	R adminResetPwd(UserVO userVO) {
		try{
			userService.adminResetPwd(userVO);
			return R.ok();
		}catch (Exception e){
			return R.error(1,e.getMessage());
		}

	}
	@GetMapping("/tree")
	@ResponseBody
	public Tree<DeptDO> tree() {
		Tree<DeptDO> tree = new Tree<DeptDO>();
		tree = userService.getTree();
		return tree;
	}

	@GetMapping("/treeView")
	String treeView() {
		return  prefix + "/userTree";
	}

	@GetMapping("/personal")
	String personal(Model model) {
		UserDO userDO  = userService.get(getUserId());
		model.addAttribute("user",userDO);
		model.addAttribute("hobbyList",dictService.getHobbyList(userDO));
		model.addAttribute("sexList",dictService.getSexList());
		return prefix + "/personal";
	}
	@ResponseBody
	@PostMapping("/uploadImg")
	R uploadImg(@RequestParam("avatar_file") MultipartFile file, String avatar_data, HttpServletRequest request) {
		Map<String, Object> result = new HashMap<>();
		try {
			result = userService.updatePersonalImg(file, avatar_data, getUserId());
		} catch (Exception e) {
			return R.error("更新图像失败！");
		}
		if(result!=null && result.size()>0){
			return R.ok(result);
		}else {
			return R.error("更新图像失败！");
		}
	}

	/**
	 * 文件导入
	 * @param file1
	 * @return
	 */
	@PostMapping(value="/importFile")
	@ResponseBody
	public R fileUpload(@RequestParam(value = "file1") MultipartFile file1){
		//判断文件是否为空
		if (!file1.isEmpty()) {
			try {
				List<UserDO> userList = new ArrayList<UserDO>();
				//调用Excel工具类里读取文件的方法，此方法是以流的形式读取的
				List<Row> list = getExcelRead(file1, true);
				//首先是读取行 也就是一行一行读，然后在取到列，遍历行里面的行，根据行得到列的值
				Map<String,Object> map =  null;
				List<Long> roleIds = null;
				for (Row row : list) {
					Cell cell_0 = row.getCell(0);
					Cell cell_1 = row.getCell(1);
					Cell cell_2 = row.getCell(2);
					Cell cell_3 = row.getCell(3);
					//得到列的值，也就是你需要解析的字段的值
					String username = getValue(cell_0);
					String name = getValue(cell_1);
					String deptName = getValue(cell_2);
					String roleName = getValue(cell_3);

					//判断用户名是否存在
					map = new HashMap<String, Object>();
					map.put("username",username);
					boolean flag = userService.exit(map);
					if(flag) {
						return R.error("账号为" + username + "的用户已存在");
					}

					//验证部门是否存在
					map.put("name",deptName);
					DeptDO deptDO = deptService.getDeptDetail(map);
					if(null == deptDO) {
						return R.error("所属部门为" + deptName + "不存在");
					}

					//验证角色是否存在
					map.put("roleName",roleName);
					RoleDO roleDO = roleService.getRoleDetail(map);
					if(null == roleDO) {
						return R.error("角色为" + roleName + "不存在");
					}

					UserDO userDO = new UserDO();
					userDO.setName(name);
					userDO.setUsername(username);
					userDO.setDeptId(deptDO.getDeptId());
					roleIds = new ArrayList<Long>();
					roleIds.add(roleDO.getRoleId());
					userDO.setRoleIds(roleIds);
					userDO.setPassword(MD5Utils.encrypt(username, "111111"));
					userDO.setStatus(1);
					userList.add(userDO);
				}

				DriverManagerDataSource dataSource=new DriverManagerDataSource();
				dataSource.setDriverClassName("com.mysql.jdbc.Driver");
				dataSource.setUrl("jdbc:mysql://" + bootdoConfig.getLocalDataBaseIp() + ":" + bootdoConfig.getRemoteDataBasePort() + "/" + bootdoConfig.getRemoteDataBaseName());
				dataSource.setUsername(bootdoConfig.getRemoteDataBaseUser());
				dataSource.setPassword(bootdoConfig.getRemoteDataBasePassword());
				JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

				//最后做个批量插入，也就是把你读取出来的数据保存到数据库中
				int saveCount = 0;
				for (UserDO data : userList) {
					saveCount += userService.save(data);
					String sql = "insert into baidujiex.upload_download_authority(user_id,upload_authority,download_authority,dept_id) values (" + data.getUserId() +",0,0,"+data.getDeptId()+")";
					jdbcTemplate.execute(sql);
				}
				if(saveCount == userList.size()) {
					return R.ok();
				}else  {
					return R.error("操作失败");
				}
			} catch (Exception e) {
				return R.error("文件读取失败");
			}
		}else{
			return R.error("文件读取失败");
		}
	}

	/**
	 * 获取解析文件行数据
	 * @param isTitle
	 * @return
	 * @throws Exception
	 */
	public static List<Row> getExcelRead(MultipartFile file1,boolean isTitle) throws Exception{
		try {
			Workbook workbook = WorkbookFactory.create(file1.getInputStream());
			Sheet sheet = workbook.getSheetAt(0);
			int count = 0;
			List<Row> list = new ArrayList<Row>();
			for (Row row : sheet) {
				// 跳过第一行的目录
				if (count == 0 && isTitle) {
					count++;
					continue;
				}
				list.add(row);
			}
			return list;
		} catch (Exception e) {
			throw e;
		}
	}

	/**
	 * 获取celL值
	 * @param cell
	 * @return
	 */
	public static String getValue(Cell cell){
		if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
			double value = cell.getNumericCellValue();
			return new BigDecimal(value).toString();
		}else if (cell.getCellType() ==HSSFCell.CELL_TYPE_STRING){
			return String.valueOf(cell.getStringCellValue());
		}else{
			return String.valueOf(cell.getStringCellValue());
		}
	}

    @GetMapping("/authorize/{id}")
	String authorize(@PathVariable("id") Long userId,Model model) {
		model.addAttribute("userId", userId);
		UserDO userDO = userService.get(userId);
		DriverManagerDataSource dataSource=new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://" + bootdoConfig.getLocalDataBaseIp() + ":" + bootdoConfig.getRemoteDataBasePort() + "/" + bootdoConfig.getRemoteDataBaseName());
		dataSource.setUsername(bootdoConfig.getRemoteDataBaseUser());
		dataSource.setPassword(bootdoConfig.getRemoteDataBasePassword());
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "select upload_authority,download_authority from baidujiex.upload_download_authority where user_id =";
		List<UserExDO> list = jdbcTemplate.query(sql + userDO.getUserId(), new RowMapper() {
			@Override
			public Object mapRow(ResultSet resultSet, int i) throws SQLException {
				UserExDO userExDO = new UserExDO();
				userExDO.setDownloadAuthority(resultSet.getString("download_authority"));
				userExDO.setUploadAuthority(resultSet.getString("upload_authority"));
				return userExDO;
			}
		});
		if(CollectionUtils.isNotEmpty(list)) {
			userDO.setUploadAuthority(list.get(0).getUploadAuthority());
			userDO.setDownloadAuthority(list.get(0).getDownloadAuthority());
		}
		model.addAttribute("userDO",userDO);
		return prefix + "/authorize";
	}

	/**
	 * 用户文件操作授权
	 * @param userDO
	 * @return
	 */
	@PostMapping("/batchAuthorize")
	@ResponseBody
	R batchAuthorize(UserDO userDO) {
		if(null != userDO.getDeptId()) {
			//修改当前部门的所有用户
			UserDO userDO1 = userService.get(userDO.getUserId());
			userDO.setDeptId(userDO1.getDeptId());
			userDO.setUserId(null);
		}
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://" + bootdoConfig.getLocalDataBaseIp() + ":" + bootdoConfig.getRemoteDataBasePort() + "/baidujiex");
        dataSource.setUsername(bootdoConfig.getRemoteDataBaseUser());
        dataSource.setPassword(bootdoConfig.getRemoteDataBasePassword());
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		StringBuffer buffer1 = new StringBuffer();
		buffer1.append("update baidujiex.upload_download_authority set upload_authority = " + userDO.getUploadAuthority());
		buffer1.append(", download_authority = " + userDO.getDownloadAuthority());

		StringBuffer buffer2 = new StringBuffer();
		buffer2.append("select user_id from baidujiex.upload_download_authority");
		if(null == userDO.getUserId() && null == userDO.getDeptId()) {
			//修改所有用户
			Map<String,Object> map = new HashMap<String, Object>();
			map.put("deptId","");
			List<UserDO> list = userService.list(map);
			if(CollectionUtils.isNotEmpty(list)) {
				//先删除所有用户信息，再插入用户信息
				String sql1 = "delete from baidujiex.upload_download_authority";
				jdbcTemplate.execute(sql1);
				for(UserDO data : list) {
					String sq2 = "insert into baidujiex.upload_download_authority(user_id,upload_authority,download_authority,dept_id) values (" + data.getUserId() +"," + userDO.getUploadAuthority()+","+userDO.getDownloadAuthority()+","+data.getDeptId() + ")";
					jdbcTemplate.execute(sq2);
				}
			}
		}
        if(null != userDO.getUserId()) {
        	//修改该当前用户
			buffer2.append(" where user_id = " + userDO.getUserId());
			//查询当前用户是否存在，存在则修改，否则添加
			List<DataBaseLogDO> list = jdbcTemplate.query(buffer2.toString(), new RowMapper() {
				@Override
				public Object mapRow(ResultSet resultSet, int i) throws SQLException {
					UserExDO userExDO = new UserExDO();
					return userExDO;
				}
			});
			if(CollectionUtils.isEmpty(list)) {
				UserDO userDO1 = userService.get(userDO.getUserId());
				String sql = "insert into baidujiex.upload_download_authority(user_id,upload_authority,download_authority,dept_id) values (" + userDO.getUserId() +"," + userDO.getUploadAuthority()+","+userDO.getDownloadAuthority() + ","+userDO1.getDeptId()+")";
				jdbcTemplate.execute(sql);
			}else {
				buffer1.append(" where user_id = " + userDO.getUserId());
				jdbcTemplate.execute(buffer1.toString());
			}
        }
        if(null != userDO.getDeptId() && null == userDO.getUserId()) {
			//修改当前部门的用户
            Map<String,Object> map = new HashMap<String, Object>();
            map.put("deptId",userDO.getDeptId());
            List<UserDO> list = userService.list(map);
            if(CollectionUtils.isNotEmpty(list)) {
				//先删除部门信息，再插入用户信息
				String sql1 = "delete from baidujiex.upload_download_authority where dept_id = " + userDO.getDeptId();
				jdbcTemplate.execute(sql1);
				for(UserDO data : list) {
					String sq2 = "insert into baidujiex.upload_download_authority(user_id,upload_authority,download_authority,dept_id) values (" + data.getUserId() +"," + userDO.getUploadAuthority()+","+userDO.getDownloadAuthority()+","+userDO.getDeptId() + ")";
					jdbcTemplate.execute(sq2);
				}
			}
        }
		return R.ok();
	}
}
